<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class User_model extends CI_Controller {

	/**
	 * Index Page for this controller.
	 *
	 * Maps to the following URL
	 * 		http://example.com/index.php/welcome
	 *	- or -  
	 * 		http://example.com/index.php/welcome/index
	 *	- or -
	 * Since this controller is set as the default controller in 
	 * config/routes.php, it's displayed at http://example.com/
	 *
	 * So any other public methods not prefixed with an underscore will
	 * map to /index.php/welcome/<method_name>
	 * @see http://codeigniter.com/user_guide/general/urls.html
	 */



	public function login_user($data)
	{
		$pword = md5($data["password"]);
		$email = $data["email"];
		$array = array('username' => $email, 'password' => $pword);
		$this->db->like($array); 	
		$query = $this->db->get('accounts_active');
		if ($query->num_rows() > 0){
		   $row = $query->row(); 
		   return $row;
		}
		redirect('', '');

	}

	/* 
		Function name: encoder_update_acad_info
		Function descriptions: Accepts subject id and updates the whole row using retrieved data
	*/

	public function view_all_students()
	{
		$this->db->select('Student_No, First_Name, Middle_Name, Last_Name, MD5_ID')->from('student')->like('Approved', 'Approved', 'none');
		$query = $this->db->get();
		return $query;
		redirect('', '');
	}

	public function get_stdno($id)
	{
		$sql = "SELECT Student_No FROM student WHERE MD5_ID LIKE '$id'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		return $row->Student_No;
		redirect('', '');
	}

	public function get_acad_info($id)
	{

		//get student's name
		$sql = "SELECT First_Name, Middle_Name, Last_Name, Curriculum_taken FROM student WHERE Student_No LIKE '$id'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$std_name = $row->Last_Name.", ".$row->First_Name." ".$row->Middle_Name; 
		$std_curriculum = $row->Curriculum_taken;

		//get reg adviser's emp no
		$sql = "SELECT EMPLOYEE_NO_INSTRUCTOR FROM reg_advisee WHERE STUDENT_NO_STUDENT LIKE '$id'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$instructor_empno = $row->EMPLOYEE_NO_INSTRUCTOR;


		//get reg adviser's name
		$sql = "SELECT INSTRUCTOR_NAME FROM faculty WHERE INSTRUCTOR_NUM LIKE '$instructor_empno'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$reg_adviser_name = $row->INSTRUCTOR_NAME; 

		//get sp adviser's emp no
		$sql = "SELECT EMPLOYEE_NO_INSTRUCTOR FROM sp_advisee WHERE STUDENT_NO_STUDENT LIKE '$id'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$instructor_empno = $row->EMPLOYEE_NO_INSTRUCTOR;

		//get sp adviser's name
		$sql = "SELECT INSTRUCTOR_NAME FROM faculty WHERE INSTRUCTOR_NUM LIKE '$instructor_empno'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$sp_adviser_name = $row->INSTRUCTOR_NAME; 

		//get all advisers' names from database
		$sql = "SELECT * FROM faculty"; 
		$query = $this->db->query($sql);
		$num_rows = $query->num_rows();
		$advisers = array();
		
		$num_rows = $query->num_rows();

		if ($num_rows > 0)
		{
		   foreach ($query->result() as $row)
		   {
			$advisers[] = $row->INSTRUCTOR_NAME;
		   }
		}

		//get subject names
		$sql = "SELECT Course_Code,	Semester_Year_Taken, Grade,	Units, Subject_Type FROM subject WHERE Student_No LIKE '$id'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		$subjects = array();
		$subject = array();

		$num_rows = $query->num_rows();

		if ($num_rows > 0)
		{
		   foreach ($query->result() as $row)
		   {
			$subject['course_code']  = $row->Course_Code;
			$subject['sem_taken']	 = substr($row->Semester_Year_Taken, 0, 2);
			$subject['yr_taken']	 = substr($row->Semester_Year_Taken, 2);
			$subject['grade']        = $row->Grade;
			$subject['units']		 = $row->Units;
			$subject['type']         = $row->Subject_Type;
			$subjects[] = $subject;
		   }
		}
		


		$sql = "SELECT MD5_ID FROM student WHERE Student_No LIKE '$id'"; 
		$query = $this->db->query($sql);
		foreach ($query->result() as $row){
			$std_id = $row->MD5_ID;
		}
		$results = array(
			'std_name'           => $std_name,
			'std_num'            => $id,
			'std_id'             => $std_id,
			'std_curriculum'     => $std_curriculum,
		    'reg_adviser'        => $reg_adviser_name,
		    'sp_adviser'         => $sp_adviser_name,
		    'subjects'           => $subjects,
		    'num_rows'           => $num_rows,
		    'adviser_names'		 => $advisers
		);

		return $results;

		redirect('', '');
	}

	/* 
		Function name: get_empno
		Function description/s: Returns the empno of an employee
	*/

	public function get_empno($name)
	{
		$sql = "SELECT INSTRUCTOR_NUM FROM faculty WHERE INSTRUCTOR_NAME LIKE '$name'"; 
		$query = $this->db->query($sql);
		$row = $query->row(); 
		return $row->INSTRUCTOR_NUM;
		redirect('', '');
	}


	/* 
		Function name: encoder_update_acad_info
		Function description/s: Accepts subject id and updates the whole row using retrieved data
	*/

	public function encoder_update_acad_info($id, $data)
	{

		$this->db->where('Subject_ID', $id);
		$this->db->update('subject', $data); 

	}

	/* 
		Function name: encoder_delete_acad_info
		Function descriptions: Accepts subject id and deletes whole rows
	*/

	public function encoder_delete_acad_info($id)
	{
		$this->db->delete('subject', array('Subject_ID' => $id)); 
	}

	/* 
		Function name: encoder_update_reg_info
		Function description/s: Accepts employee numbers of advisers and updates the student's adviser and curriculum data
	*/

	public function encoder_update_reg_info($data)
	{

		$reg_adviser_empno = $data['reg_adviser_empno']; 
		$reg_adviser_id = $data['reg_adviser_id'];
		$sp_adviser_empno =	$data['sp_adviser_empno'];
		$sp_adviser_id = $data['sp_adviser_id'];
		$std_curriculum = $data['std_curriculum'];
		$std_num = $data['std_num'];
		$std_id = $data['std_id'];

		$update_data = array(
           'EMPLOYEE_NO_INSTRUCTOR' => $reg_adviser_empno,
           'EMPNO_STDNO' => $reg_adviser_id
        );

		$this->db->where('STUDENT_NO_STUDENT', $std_num);
		$this->db->update('reg_advisee', $update_data); 

		$update_data = array(
           'EMPLOYEE_NO_INSTRUCTOR' => $sp_adviser_empno,
           'EMPNO_STDNO' => $sp_adviser_id
        );

		$this->db->where('STUDENT_NO_STUDENT', $std_num);
		$this->db->update('sp_advisee', $update_data); 

		$update_data = array(
           'Curriculum_taken' => $std_curriculum
        );

		$this->db->where('Student_No', $std_num);
		$this->db->update('student', $update_data); 

		return;
		// Produces:
		// UPDATE mytable 
		// SET title = '{$title}', name = '{$name}', date = '{$date}'
		// WHERE id = $id

	}

	
	
	
//localhost/SAIS_CI_Final/user_model
}

/* End of file user_model.php */
/* Location: ./application/controllers/user_model.php */